package io.potato.ts.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import io.potato.core.CrudService;
import io.potato.core.util.LdtUtil;
import io.potato.core.util.Strings;
import io.potato.core.util.TableUtil;
import io.potato.ts.common.Constants;
import io.potato.ts.common.SmsIdWorker;
import io.potato.ts.domain.SmsSendingRecord;
import io.potato.ts.repository.SmsSendingRecordRepository;
import lombok.extern.slf4j.Slf4j;


/**
 * 等待发送的短信服务类
 * 
 * @author timl
 *
 * <p>2019-01-07 15:13:58</p>
 */
@Service
@Transactional
@Slf4j
public class SmsSendingRecordService extends CrudService<SmsSendingRecord, Long> {

	@Autowired
	SmsSendingRecordRepository repository;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	protected JpaRepository<SmsSendingRecord, Long> getRepository() {
		return repository;
	}
	
	/**
	 * ID生成器
	 */
	@Autowired
	private SmsIdWorker idWorker;
	
	private static final String SELECT_COLUMN = "select id, msg_id, user_code, batch_code, extend_code, dest_num, content, send_time, need_report, task_name, sms_type from ";
	
	private int queryBatchSize = 20000;
	
	public SmsSendingRecord newWebSmsRecord(String userCode, String extendCode, String content, String mobileNo, String taskName, Integer smsType, LocalDateTime sendTime) {
		SmsSendingRecord sendingRec = new SmsSendingRecord();
		sendingRec.setUserCode(userCode);
		sendingRec.setBatchCode(userCode);
		sendingRec.setExtendCode(extendCode);
		sendingRec.setSenderType(Constants.SENDER_TYPE_WEB);
		sendingRec.setSendTime(sendTime);
		sendingRec.setContent(content);
		sendingRec.setDestNum(mobileNo);
		sendingRec.setLot(Strings.uuid());
		sendingRec.setStatus(Constants.SendingStatus.TO_BE_SEND);
		sendingRec.setSeq(NumberUtils.toInt(userCode, 0) % Constants.SEND_WEB_SMS_NUM);
		sendingRec.setTryTimes(0);
		sendingRec.setSmsType(smsType);
		sendingRec.setMsgId("" + idWorker.nextId());
		sendingRec.setNeedReport(Constants.NeedReport.YES);
		sendingRec.setTaskName(taskName);
		return sendingRec;
	}
	
	/**
	 * 查询到期要发送的记录， 发送时间在指定时间之前的没有发送的记录
	 * @param sendTime  发送时间
	 * @return
	 */
	public List<SmsSendingRecord> findRecordToSend(LocalDateTime sendTime, String table) {
		String sql =  SELECT_COLUMN + table + " where send_time <= ? AND status = 0 AND try_times = 0 limit ? ";
		return jdbcTemplate.query(sql, new Object[] {sendTime, queryBatchSize}, new ObjectRowMapper());
	}
	
	public List<SmsSendingRecord> findRecordToSend(LocalDateTime sendTime, int seq,  String table) {
		String sql =  SELECT_COLUMN + table + " where send_time <= ? AND status = 0 AND try_times = 0 AND seq = ? limit ? ";
		return jdbcTemplate.query(sql, new Object[] {sendTime, seq, queryBatchSize}, new ObjectRowMapper());
	}
	
	/**
	 * 查找发送失败的记录
	 * @param sendTime
	 * @param tryTimes
	 * @param tryAt
	 * @return
	 */
	public List<SmsSendingRecord> findFailedToSend(LocalDateTime sendTime, Integer tryTimes, LocalDateTime tryAt, String table) {
		String sql =  SELECT_COLUMN + table + " where send_time <= ? AND status = 0 AND try_times = ? AND try_at <= ?  limit ? ";
		return jdbcTemplate.query(sql, new Object[] {sendTime, tryTimes, tryAt, queryBatchSize}, new ObjectRowMapper());
	}
	
	private static class ObjectRowMapper implements RowMapper<SmsSendingRecord> {

		@Override
		public SmsSendingRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
			SmsSendingRecord row = new SmsSendingRecord();
			row.setId(rs.getLong(1));
			row.setMsgId(rs.getString(2));
			row.setUserCode(rs.getString(3));
			row.setBatchCode(rs.getString(4));
			row.setExtendCode(rs.getString(5));
			row.setDestNum(rs.getString(6));
			row.setContent(rs.getString(7));
			row.setSendTime(LdtUtil.toLocalDateTime(rs.getTimestamp(8)));
			row.setNeedReport(rs.getInt(9));
			row.setTaskName(rs.getString(10));
			row.setSmsType(rs.getInt(11));
			return row;
		}
		
	}
	
	/**
	 * 修改状态
	 * @param ids   要修改的记录ID
	 * @param status  0 待发送  1 已发送 2 已暂停 -1 已取消 -2 已删除
	 */
	public void updateStatus(String userCode, Long id, Integer status) {
		String table = TableUtil.getSendingRecordTable(userCode);
		String sql = "update " + table + " set status = ? where id = ?";
		jdbcTemplate.update(sql, status, id);
	}
	
	/**
	 * 批量修改状态
	 * @param ids   要修改的记录ID
	 * @param status  0 待发送  1 已发送 2 已暂停 -1 已取消 -2 已删除
	 */
	public void updateStatus(String userCode, List<Long> ids, Integer status) {
		if (ids == null || ids.isEmpty()) {
			return;
		}
		String table = TableUtil.getSendingRecordTable(userCode);
		String sql = String.format("update " + table + " set status = %s where id in (%s)", status, Strings.join(ids, ','));
		jdbcTemplate.update(sql);
	}
	
	/**
	 * 更新尝试发送次数
	 * @param ids 要修改的记录ID
	 */
	public void updateTryTimes(String userCode, List<Long> ids) {
		if (ids == null || ids.isEmpty()) {
			return;
		}
		
		String table = TableUtil.getSendingRecordTable(userCode);
		String sql = String.format("update " + table + " set try_times = try_times + 1, try_at = now() where id in (%s)", Strings.join(ids, ','));
		jdbcTemplate.update(sql);
	}
	
	/**
	   *     批量删除记录
	 * @param ids
	 */
	public void deleteBatch(String userCode, List<Long> ids) {
		if (ids == null || ids.isEmpty()) {
			return;
		}
		
		String table = TableUtil.getSendingRecordTable(userCode);
		String sql = String.format("delete from " + table + " where id in (%s)", Strings.join(ids, ','));
		jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据条件查询待发送记录
	 * @param pageable  分页信息
	 * @param userCode  用户账号
	 * @param taskName  任务名称
	 * @param sendingStatus  状态
	 * @return  
	 */
	public Page<Map<String, Object>> findUserSendingRecord(Pageable pageable, String userCode, String taskName, Integer sendingStatus) {
		List<Object> argList = new ArrayList<>();
		argList.add(userCode);
		
		String where = " t.user_code = ? ";
		where += " AND t.status = " + sendingStatus;
		where += " AND t.sms_type = 1 ";
		
		if (!StringUtils.isEmpty(taskName)) {
			where += " AND t.task_name like ? ";
			argList.add("%" + taskName + "%");
		}
		
		String table = TableUtil.getSendingRecordTable(userCode);
		
		String select = "t.id, t.user_code userCode, t.dest_num destNum, t.status, t.task_name taskName, t.content, t.send_time sendTime ";
		
		String sql = "select " + select + ", t1.name destName, \n" + 
				"case when t.status = 0 then '待发送'  when t.status=-1 then '已取消'  when t.status=2 then '已取消'  end statusDesc \n" + 
				"  from " + table + " t\n" + 
				"left join t_contacts t1\n" + 
				"on t.user_code = t1.user_code and t.dest_num = t1.mobile_no \n" + 
				"where \n" + where +
 				" group by t.id limit " + 
				pageable.getOffset() + ", " + 
 				pageable.getPageSize();
		
		Object[] args = argList.toArray();
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, args);
		
		String countSql = "select count(*) from " + table + " t where " + where;
		long count = this.jdbcTemplate.queryForObject(countSql, args, Long.class);
		
		return new PageImpl<>(list, pageable, count);
	}
	
}
